Лабораторна робота № 2
Визначення оптимальної ціни виробу і об’єму виробництва продукції Введення формул і функцій у комірки та робота з базами даних в MS Excel
Мета роботи. Набуття навичок практичної роботи з обґрунтування показників виробничо-збутової діяльності з допомогою прикладної програми MS Excel, що входить у Microsoft Office і широко застосовується для здійснення розрахунків.
Провести додаткове дослідження (Див. попередню лабораторну роботу) при розширенні номенклатури до чотирьох видів продукції. За умови, що ціни на вироби B, C, D пов’язані з оптимальною оптовою ціною продажу виробу А за схемою, що представлена в таблиці 1, де ціна виробу А рівна 100 %.
Таблиця 1.
Змінні витрати на закупівалю інгредієнтів для виробів B, C, D.
При розрахунку ціни виробів B, C, D в моделі Ms Excel (де вказуються змінні управлінського рішення) їх необхідно задати як функцію від ціни виробу А. Початкова ціна виробу А в рядку Змінні управлінського рішення Моделі 2 визначається на підставі аналізу чутливості проведеного в попередній лабораторній роботі.
Ціна для побудови рівняння попиту для виробів B, C, D визначається на підставі генерації випадкових чисел за умови нормального розподілу (Див. рис. 1). Де N – номер варіанту студента. Значення Z та K для виробів B, C, D беремо з таблиці 2.
Після генерування значення цінових параметрів для виробів B, C, D необхідно відсортувати в спадному порядку.
Величина змінних виробничих витрат (на випічку, упакування і збут одиниці продукції) по виробах B, C, D залежить від об’ємів виробництва та встановлюється на підставі даних Таблиці 3. Кількісне значення вищезгаданих залежностей необхідно визначити на підставі лінійного однофакторного кореляційно-регресійного аналізу графічним методом за допомогою засобів Ms Excel.
Таблиця 3.
Окремі показники виробничої діяльності фірми по виробах B, C, D.
* для студентів використовувати знак „+” за умови, якщо номер групи в якій вони навчаються є парним числом. У іншому випадку використовувати знак „–”.
Максимальна виробнича потужність фірми при однозмінному робочому дні становить 25 тис. шт. виробів за тиждень. Для кожного наступного виробу до виробничих витрат додаються додаткові витрати в розмірі 0,8 +N/80 грн., пов’язані з введенням другої зміни.
Порядок виконання роботи:
За допомогою графічних засобів Ms Excel, методом лінійного однофакторного кореляційно-регресійного аналізу на підставі даних Таблиці 3, визначити вплив:
зміни ціни на величину тижневого попиту для виробів В,С, D;
зміни обсягу виробництва продукції на тижневу величину витрат для виробів В,С, D.
На підставі додаткових даних внести зміни у вихідну модель. Для врахування витрат пов’язаних з визначенням необхідності роботи у двозмінному режимі скористайтеся логічною функцією ЕСЛИ;
За допомогою команди Вставка/Діаграма... відобразіть структуру ціни та собівартості всіх видів продукції використавши гістограму з накопиченням і нормовану гістограму. Суму наднормових (що виникають у випадку роботи у двозмінному режимі) та постійних витрат для кожного виробу необхідно визначити пропорційно. За базу пропорційності, яку студент вибирає на свій розсуд, можна взяти об’єм виробництва продукції, ціну виробів чи один з елементів змінних витрат;
За допомогою команди Таблицы подстановки Ms Excel з двома входами обґрунтуйте можливість зменшення втрат від понаднормових робіт (в діапазоні від 20 до 30 тис. шт. виробів за тиждень з кроком 1 тисяча штук) за умови зміни цін (в діапазоні від 8 до 10 грн. за одиницю продукції А з кроком 0,1 грн.) і відповідно падіння попиту;
За допомогою команди Таблицы подстановки Ms Excel з одним входом визначити на скільки зміниться попит, виручка, наднормові витрати, прибуток, якщо вдасться збільшити виробничу потужність від 20 до 30 тис. штук з кроком 1 тис. шт.;
На підставі даних Таблицы подстановки Ms Excel з двома входами (Див пункт 4 даної лабораторної роботи) побудуйте об’ємний і точковий графіки прибутковості виробництва.
На підставі даних Таблицы п...
